﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using Core.DAL;
using Core.Extentions;
using Core.Utils;

namespace Core.BOL
{
    public class CalendarRepository
    {
        private ScoutNetDataContext context = null;

        #region Construtor
        public CalendarRepository()
        {
            context = GeneralUtils.GetDataContext();
        }
        #endregion

        #region Methodes
        public void RemoveConvocReference(int CID)
        {
            var query = (from m in context.tSite_Calendars
                         where m.CID == CID
                         select m).ToList();
            
            if(query.Count > 0)
            {
                foreach (var item in query)
                {
                    item.CID = null;
                }
            }
        }

        public void InsertMeeting(tSite_Calendar calendar)
        {
            context.tSite_Calendars.InsertOnSubmit(calendar);
        }

        public tSite_Calendar GetEventsById(int ID)
        {
            var query = (from c in context.tSite_Calendars
                         join s in context.tSite_Sections on c.SID equals s.ID
                         where c.ID == ID
                         select c).SingleOrDefault();
            return query;
        }

        public List<tSite_Calendar> GetEventsBySystemNameAndDate(string systemName, int month, int year)
        {
            DateTime startDate = Convert.ToDateTime(month + "/01/" + year + " 00:00:00", CultureInfo.InvariantCulture);
            DateTime endDate = Convert.ToDateTime(month + "/" + DateTime.DaysInMonth(year, month) + "/" + year + " 23:59:59", CultureInfo.InvariantCulture);

            var query = (from c in context.tSite_Calendars
                         join s in context.tSite_Sections on c.SID equals s.ID
                         where (s.SystemName == systemName) &&
                         (
                             (c.StartDate >= startDate && c.StartDate <= endDate) || (c.EndDate >= startDate && c.EndDate <= endDate)
                         )
                         orderby c.StartDate ascending
                         select c);
            return query.ToList();
        }

        public List<sp_Site_GetNextMeetingsResult> GetNextEventsFromDate(DateTime currentDate)
        {
            return context.sp_Site_GetNextMeetings(currentDate, currentDate.AddDays(7)).OrderBy(p => p.SID).ToList();
        }

        public List<sp_Site_GetNextMeetingsResult> GetNextEvents()
        {
            return GetNextEventsFromDate(DateTime.Now.GetNowTimeByTimeZone()).ToList();
        }

        public List<tSite_Calendar> GetNextEventsBySystemName(string systemName)
        {
            return GetNextEventsBySystemName(systemName, -1);
        }

        public List<tSite_Calendar> GetNextEventsBySystemName(string systemName, int numItems)
        {
            var query = (from c in context.tSite_Calendars
                         join s in context.tSite_Sections on c.SID equals s.ID
                         where (s.SystemName.ToLower() == systemName.ToLower()) &&
                         (
                             (c.StartDate >= DateTime.Now.GetNowTimeByTimeZone() || c.EndDate >= DateTime.Now.GetNowTimeByTimeZone()) 
                         )
                         orderby c.StartDate
                         select c);
            if (numItems != -1)
            {
                query = query.Take(numItems);
            }

            return query.ToList();
        }

        public tSite_Calendar GetFirstEventBySection(string systemName)
        {
            var query = (from c in context.tSite_Calendars
                         orderby c.StartDate
                         select c).Take(1).SingleOrDefault();
            return query;
        }

        public tSite_Calendar GetLastEventBySection(string systemName)
        {
            var query = (from c in context.tSite_Calendars
                         orderby c.StartDate descending
                         select c).Take(1).SingleOrDefault();
            return query;
        }

        public List<tSite_Calendar> GetLastestInsertedEventBySystemName(string systemName, int numItems)
        {
            var query = (from c in context.tSite_Calendars
                         join s in context.tSite_Sections on c.SID equals s.ID
                         where s.SystemName == systemName
                         orderby c.InsertDate descending
                         select c);

            if (numItems != -1)
            {
                query = query.Take(numItems);
            }

            return query.ToList();
        }

        public List<tSite_Calendar> GetEventsByDate(int month, int year)
        {
            DateTime startDate = Convert.ToDateTime(month + "/01/" + year, CultureInfo.InvariantCulture);
            DateTime endDate = Convert.ToDateTime(month + "/" + DateTime.DaysInMonth(year, month) + "/" + year, CultureInfo.InvariantCulture);

            var query = (from c in context.tSite_Calendars
                         join s in context.tSite_Sections on c.SID equals s.ID
                         where
                         (
                             (c.StartDate >= startDate && c.StartDate <= endDate) || (c.EndDate >= startDate && c.EndDate <= endDate)
                         )
                         orderby c.StartDate ascending
                         select c).ToList();
            return query;
        }

        public void DeleteMeeting(int id)
        {
            tSite_Calendar tMeeting = context.tSite_Calendars.SingleOrDefault(p => p.ID == id);
            if (tMeeting != null)
            {
                context.tSite_Calendars.DeleteOnSubmit(tMeeting);
            }
        }

        public void Save()
        {
            context.SubmitChanges();
        }
        #endregion
    }
}
